﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Globalization;


public partial class Employees : System.Web.UI.Page
{
    DataTable dt;
    DataView dv;
    SqlConnection con = new SqlConnection();
    protected void Page_Load(object sender, EventArgs e)
    {

        try
        {
            if (this.IsPostBack == false)
            {
                LoadEmployees();
                txtBirthDay.Text = DateTime.Now.ToString("MM/dd/yyyy");
                txtHireDay.Text = DateTime.Now.ToString("MM/dd/yyyy");
                GetDataSource_mgrID();
                btnDelete.Enabled = false;
                btnUpdate.Enabled = false;
                btnReload.Enabled = false;
                btnAdd.Enabled = true;
                btnNew.Enabled = true;
                Session["isSelected"] = false;
            }

        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Can't Load" + "')</script>");
        }
    }

    #region My Code
    void LoadEmployees()
    {

        try
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM HR.Employees", con.ConnectionString);
            dt = new DataTable("HR.Employees");
            da.Fill(dt);

            gvEmployees.AutoGenerateSelectButton = true;
            gvEmployees.AllowPaging = true;
            gvEmployees.PageSize = 5;
            gvEmployees.DataSource = dt;
            gvEmployees.DataBind();
            txtID.Enabled = false;
            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + "Can't Load Employees" + "')</script>");
        }
    }

    void AddEmployees()
    {
        try
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Employees_Insert";

            SqlParameter param = new SqlParameter("@lastname", SqlDbType.NVarChar);
            param.Size = 20;
            param.Value = txtLastName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@firstname", SqlDbType.NVarChar);
            param.Size = 10;
            param.Value = txtFirstName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@title", SqlDbType.NVarChar);
            param.Size = 30;
            param.Value = DDLTittle.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar);
            param.Size = 25;
            param.Value = DDLTitleOfCourtesty.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@birthdate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtBirthDay.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@hiredate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtHireDay.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@address", SqlDbType.NVarChar);
            param.Size = 60;
            param.Value = txtAddress.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@city", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = txtCity.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@region", SqlDbType.NVarChar);
            param.Size = 15;
            if (txtRegion.Text.Trim() != "")
            {
                param.Value = txtRegion.Text.Trim();
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
            param.Size = 10;
            if (txtPostalCode.Text.Trim() != "")
            {
                param.Value = txtPostalCode.Text.Trim();
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@country", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = DDLCountry.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@phone", SqlDbType.NVarChar);
            param.Size = 24;
            param.Value = txtPhone.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@mgrid", SqlDbType.Int);
            if (ckbManagerName.Checked == false)
            {
                param.Value = Int32.Parse(DDLManagerName.Text);
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script>alert('" + "Add successfull" + "')</script>");
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            con.Close();
        }


    }

    void UpdateEmployees()
    {
        try
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Employees_Update";

            SqlParameter param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = txtID.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@lastname", SqlDbType.NVarChar);
            param.Size = 20;
            param.Value = txtLastName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@firstname", SqlDbType.NVarChar);
            param.Size = 10;
            param.Value = txtFirstName.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@title", SqlDbType.NVarChar);
            param.Size = 30;
            param.Value = DDLTittle.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar);
            param.Size = 25;
            param.Value = DDLTitleOfCourtesty.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@birthdate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtBirthDay.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@hiredate", SqlDbType.DateTime);
            param.Value = DateTime.Parse(txtHireDay.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@address", SqlDbType.NVarChar);
            param.Size = 60;
            param.Value = txtAddress.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@city", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = txtCity.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@region", SqlDbType.NVarChar);
            param.Size = 15;
            if (txtRegion.Text.Trim() != "")
            {
                param.Value = txtRegion.Text.Trim();
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
            param.Size = 10;
            if (txtPostalCode.Text.Trim() != "")
            {
                param.Value = txtPostalCode.Text.Trim();
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@country", SqlDbType.NVarChar);
            param.Size = 15;
            param.Value = DDLCountry.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@phone", SqlDbType.NVarChar);
            param.Size = 24;
            param.Value = txtPhone.Text.Trim();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@mgrid", SqlDbType.Int);
            if (ckbManagerName.Checked == false)
            {
                param.Value = Int32.Parse(DDLManagerName.Text);
            }
            else
            {
                param.Value = DBNull.Value;
            }
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script>alert('" + "Update successful" + "')</script>");

        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            con.Close();
        }
    }

    void DeleteEmployees()
    {
        try
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_Employees_Delete";

            SqlParameter param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = txtID.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script>alert('" + "Delete successful" + "')</script>");
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
            con.Close();
        }
    }

    void ClearEmployees()
    {
        try
        {
            txtID.Text = "";
            txtLastName.Text = "";
            txtFirstName.Text = "";
            DDLTittle.SelectedIndex = 0;
            DDLTitleOfCourtesty.SelectedIndex = 0;
            txtBirthDay.Text = DateTime.Now.ToString("MM/dd/yyyy");
            txtHireDay.Text = DateTime.Now.ToString("MM/dd/yyyy");
            txtAddress.Text = "";
            txtCity.Text = "";
            txtRegion.Text = "";
            txtPostalCode.Text = "";
            DDLCountry.SelectedIndex = 0;
            txtPhone.Text = "";
            DDLManagerName.SelectedIndex = 0;
            //Session["isSelected"] = false;
            btnDelete.Enabled = false;
            btnUpdate.Enabled = false;
            btnAdd.Enabled = true;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }

    void GetDataSource_mgrID()
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT empid,firstname + ' ' + lastname AS fullname FROM HR.Employees";
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source, "HR.Employees");
            da.Fill(ds, "HR.Employees");
            DataTable dt = ds.Tables["HR.Employees"];
            da.Fill(dt);
            DDLManagerName.DataSource = dt;
            DDLManagerName.DataTextField = "fullname";
            DDLManagerName.DataValueField = "empid";
            DDLManagerName.DataBind();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }

    void SearchEmployees()
    {
        try
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            if (txtSearch.Text != "")
            {
                if (DDLSearch.SelectedIndex == 0)
                {
                    Response.Write("<script>alert('" + "Choose Name or Title" + "')</script>");
                }
                else
                {
                    if (DDLSearch.SelectedIndex == 1)
                    {
                        cmd.CommandText = string.Format("SELECT * FROM HR.Employees WHERE firstname + ' ' + lastname like'%{0}%'", txtSearch.Text);
                    }
                    else
                    {
                        cmd.CommandText = string.Format("SELECT * FROM HR.Employees WHERE title like'%{0}%'", txtSearch.Text);
                    }
                }
            }
            else
            {
                Response.Write("<script>alert('" + "Please input" + "')</script>");
            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            dt = new DataTable("HR.Employees");
            da.Fill(dt);

            gvEmployees.DataSource = dt;
            gvEmployees.DataBind();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }

    bool ValidateBirthDate()
    {
        try
        {
            if (DateTime.Now.Year - DateTime.Parse(txtBirthDay.Text).Year < 18)
            {
                //Response.Write("<script>alert('" + "Birth Day Under 18" + "')</script>");
                return false;
            }

            if (DateTime.Now.Year - DateTime.Parse(txtBirthDay.Text).Year == 18)
            {
                if (DateTime.Now.Month > DateTime.Parse(txtBirthDay.Text).Month)
                {
                    //Response.Write("<script>alert('" + "Birth Day Under 18" + "')</script>");
                    return false;
                }
            }
            if (DateTime.Now.Year - DateTime.Parse(txtBirthDay.Text).Year == 18)
            {
                if (DateTime.Now.Month == DateTime.Parse(txtBirthDay.Text).Month)
                {
                    if (DateTime.Now.Day > DateTime.Parse(txtBirthDay.Text).Day)
                    {
                        //Response.Write("<script>alert('" + "Birth Day Under 18" + "')</script>");
                        return false;
                    }
                }
            }
            
        }
        catch (Exception)
        {

        }
        return true;
    }

    bool ValidateHireDate()
    {
        try
        {
            if (DateTime.Parse(txtHireDay.Text).Year - DateTime.Parse(txtBirthDay.Text).Year < 18)
            {
                //Response.Write("<script>alert('" + "Hire Day Under 18" + "')</script>");
                return false;
            }
            if (DateTime.Parse(txtHireDay.Text).Year - DateTime.Parse(txtBirthDay.Text).Year == 18)
            {
                if (DateTime.Parse(txtHireDay.Text).Month < DateTime.Parse(txtBirthDay.Text).Month)
                {
                    //Response.Write("<script>alert('" + "Hire Day Under 18" + "')</script>");
                    return false;
                }
            }
            if (DateTime.Parse(txtHireDay.Text).Year - DateTime.Parse(txtBirthDay.Text).Year == 18)
            {
                if (DateTime.Parse(txtHireDay.Text).Month == DateTime.Parse(txtBirthDay.Text).Month)
                {
                    if (DateTime.Parse(txtHireDay.Text).Day < DateTime.Parse(txtBirthDay.Text).Day)
                    {
                        //Response.Write("<script>alert('" + "Hire Day Under 18" + "')</script>");
                        return false;
                    }
                }
            }
            
        }
        catch (Exception)
        {

        }
        return true;
    }
    #endregion

    #region Event
    protected void gvEmployees_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            if ((bool)Session["Load"] == true)
            {
                gvEmployees.PageIndex = e.NewPageIndex;
                btnSearch_Click(sender, e);
            }
            else
            {
                gvEmployees.PageIndex = e.NewPageIndex;
                LoadEmployees();
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + "Failed Page Index Changing" + "')</script>");
        }
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        try
        {
            if (IsValid)
            {
                AddEmployees();
                LoadEmployees();
            }
            else
            {
                cvBirthDate.ErrorMessage = "Under 18";
                cvHireDay.ErrorMessage = "Under 18";
            }
        }
        catch (Exception ex)
        {

            Response.Write("<script>alert('" + "Failed Add" + "')</script>");
        }
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        try
        {
            UpdateEmployees();
            LoadEmployees();
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + "Failed Update" + "')</script>");
        }
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        try
        {
            DeleteEmployees();
            LoadEmployees();
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + "Failed Delete" + "')</script>");
        }
    }
    protected void gvEmployees_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            btnAdd.Enabled = false;
            btnUpdate.Enabled = true;
            btnDelete.Enabled = true;
            GridViewRow r = gvEmployees.SelectedRow;
            txtID.Text = r.Cells[1].Text;
            txtLastName.Text = Server.HtmlDecode(r.Cells[2].Text);
            txtFirstName.Text = Server.HtmlDecode(r.Cells[3].Text);
            DDLTittle.Text = r.Cells[4].Text;
            DDLTitleOfCourtesty.Text = r.Cells[5].Text;
            txtBirthDay.Text = r.Cells[6].Text;
            txtHireDay.Text = r.Cells[7].Text;
            txtAddress.Text = Server.HtmlDecode(r.Cells[8].Text);
            txtCity.Text = Server.HtmlDecode(r.Cells[9].Text);
            txtRegion.Text = Server.HtmlDecode(r.Cells[10].Text);
            txtPostalCode.Text = Server.HtmlDecode(r.Cells[11].Text);
            DDLCountry.Text = r.Cells[12].Text;
            txtPhone.Text = r.Cells[13].Text;
            if ((Server.HtmlDecode(r.Cells[14].Text)).Trim() != "")
            {
                DDLManagerName.Enabled = true;
                ckbManagerName.Checked = false;
                DDLManagerName.Text = Server.HtmlDecode(r.Cells[14].Text);
            }
            else
            {
                DDLManagerName.Enabled = false;
                ckbManagerName.Checked = true;
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + "Failed Selected Index Changed" + "')</script>");
        }

    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        try
        {
            ClearEmployees();
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + "Failed New" + "')</script>");
        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        try
        {
            SearchEmployees();
            Session["Load"] = true;
            btnReload.Enabled = true ;
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + "Failed Search" + "')</script>");
        }
    }
    protected void btnReload_Click(object sender, EventArgs e)
    {
        try
        {
            txtSearch.Text = "";
            LoadEmployees();
            Session["Load"] = false;
            btnReload.Enabled = false;
        }
        catch (Exception)
        {
            Response.Write("<script>alert('" + "Failed Reload" + "')</script>");
        }
    }
    #endregion


    protected void cvBirthDate_ServerValidate(object source, ServerValidateEventArgs args)
    {
        try
        {
            if (ValidateBirthDate() == true)
            {
                args.IsValid = true;
            }
            else
                args.IsValid = false;
        }
        catch (Exception)
        {

        }
    }
    protected void cvHireDay_ServerValidate(object source, ServerValidateEventArgs args)
    {
        try
        {
            if (ValidateHireDate() == true)
            {
                args.IsValid = true;
            }
            else
                args.IsValid = false;
        }
        catch (Exception)
        {

        }
    }
}
